** This dofile is to randomize mentor-mentee 



********************************************************************************
*
*						GRADE 12 STUDENTS 
*
********************************************************************************
set seed 10101

* Open mentor dataset 
import excel using "$raw/manual/TVET_selected_3rd_Oct.xlsx", describe
	local name=substr("`excelfile'", 1, length("`excelfile'") - 5) 
	
	* Open each sheet of the excel file
	forvalues sheet=1/`=r(N_worksheet)' {  
	local sheetname=r(worksheet_`sheet')  
	import excel using "$raw/manual/TVET_selected_3rd_Oct.xlsx", sheet("`sheetname'") clear 
	save "$temp/file_`name'_`sheetname'", replace  
	clear
	}
	
	* keep necessary information
	global tempfilelist: dir "$temp/" files "file_*.dta"
	foreach tfile of global tempfilelist {
	use "$temp/`tfile'", clear
	cap keep A-H
	save, replace
	}

	clear
	
	global tempfilelist: dir "$temp/" files "file_*.dta"
	foreach tfile of global tempfilelist {
    append using "$temp/`tfile'", force
	erase "$temp/`tfile'"
	}
	
	keep B C D F H
	
	rename B mentor_name
	rename C mentor_email
	rename D mentor_stream 
	rename F mentor_tti
	rename H mentor_phone 
	
	drop if regexm(mentor_name, "Name|name")
	drop if mentor_name==""
	
	* create mentorid 
	g tti_code = "TY" if mentor_tti=="Trashiyangtse zorgi chusum"
	replace tti_code = "TY" if mentor_tti=="Zorig Chusum, Tashi Yangtse"
	replace tti_code = "KS" if inlist(mentor_tti, "Khuruthang Technical Training Institute", "Samthang Technical Training Institute", "Technical Training Institute- chumey")
	replace tti_code = "TP" if inlist(mentor_tti, "Thimphu National Institute for Zorig Chusum", "Thimphu Technical Institute", "Thimphu National Institute of Zorig Chusum", "Thimphu National Institute for Zorig Chuzum")
	tab tti_code
	
	set seed 10101
	bys tti_code: g rand_num=runiform()
	bys tti_code: egen ordering=rank(rand_num)
	tostring ordering, replace
	replace ordering = "0" + ordering if strlen(ordering)==1
	g mentorid = "MT" + tti_code + ordering 
	
	keep mentor*
	
	save "$randomization/randomization_mentor_TVET.dta", replace

* Open mentee dataset 
import excel using "$randomization/randomization_student_TVET_mainlist.xlsx", sheet("Sheet1") firstrow clear

* TTI codes 
merge m:1 schoolname using "$clean/list_of_schools.dta", keepusing(tti*) keep(match) nogen

g tti_code = "TY" if tti_tyangste==1
replace tti_code = "KS" if tti_ks==1
replace tti_code = "TP" if tti_thimphu==1

set seed 10101
bys schoolname: g rand_num=runiform()
bys schoolname: egen ordering=rank(rand_num)

g mentor = .
forval i = 01/25 {
	sca l = (`i'-1)*5
	sca h = `i'*5
	dis "`=scalar(l)'"
	dis "`=scalar(h)'"
	replace mentor = `i' if ordering >`=scalar(l)' & ordering<=`=scalar(h)'
}
sort tti_code schoolname mentor
egen groupmentor1 = group(tti_code schoolname mentor) if tti_code=="KS"
egen groupmentor2 = group(tti_code schoolname mentor) if tti_code=="TY"
egen groupmentor3 = group(tti_code schoolname mentor) if tti_code=="TP"
egen groupmentor=rowmax(groupmentor1 groupmentor2 groupmentor3)
tostring groupmentor, replace
replace groupmentor = "0" + groupmentor if strlen(groupmentor)==1

g mentorid = "MT" + tti_code + groupmentor 

keep schoolname stream name studentid sex b_day b_month b_year phone mentorid

* merge with mentor 
merge m:1 mentorid using "$randomization/randomization_mentor_TVET.dta"
drop _merge

sort mentorid name
by mentorid: g id=_n
tostring id, replace
replace id = "0" + id if strlen(id)==1

g menteeid = mentorid + id
drop id

sort mentorid mentor_name mentor_email menteeid schoolname name studentid 
order mentorid mentor_name mentor_email mentor_phone menteeid schoolname name studentid 

export excel using "$randomization/final/randomization_TVET_mainlist_$date.xlsx", firstrow(variables) replace


********************************************************************************
*
*						GRADE 10 STUDENTS 
*
********************************************************************************
* Open mentor dataset 
import excel using "$raw/manual/STEM_selected_9th_Oct.xlsx", describe
	local name=substr("`excelfile'", 1, length("`excelfile'") - 5) 
	
	* Open each sheet of the excel file
	forvalues sheet=1/`=r(N_worksheet)' {  
	local sheetname=r(worksheet_`sheet')  
	import excel using "$raw/manual/STEM_selected_9th_Oct.xlsx", sheet("`sheetname'") clear 
	save "$temp/file_`name'_`sheetname'", replace  
	clear
	}
	
	* keep necessary information
	global tempfilelist: dir "$temp/" files "file_*.dta"
	foreach tfile of global tempfilelist {
	use "$temp/`tfile'", clear
	cap keep B-F
	save, replace
	}

	clear
	
	global tempfilelist: dir "$temp/" files "file_*.dta"
	foreach tfile of global tempfilelist {
    append using "$temp/`tfile'", force
	erase "$temp/`tfile'"
	}
	
	rename B mentor_name
	rename C mentor_email
	rename E mentor_stem
	rename D mentor_phone 
	rename F mentor_stream
	
	drop if regexm(mentor_name, "Name|name")
	drop if mentor_name==""
	
	* create mentorid 
	g stem_code = ""
	replace stem_code="CST" if inlist(mentor_stem, "College of Science and Technology", "College of Science and Technology ", "College of science and Technology")
	replace stem_code = "JNE" if inlist(mentor_stem, "Jigme Namgyal Engineering College", "Jigme Namgyel Engineering College")
	replace stem_code = "GCI" if inlist(mentor_stem, "Gyalpozhing College of Information and Technology", "Gyalpozhing college of information technology", "Gyalpozhing College of Information Technology")
	replace stem_code = "SHR" if mentor_stem=="Sherubtse College"
	replace stem_code = "CNR" if mentor_stem=="College of Natural Resources"
	replace stem_code = "RTC" if inlist(mentor_stem, "Royal Thimphu College", "Gujarat Technological University", "Khesar Gyalpo University of Medical Science", "Bfit Collage Affiliated to HNB Gharwal University Dehradun,india.", "Asian University for Women (AUW) – Chittagong, Bangladesh", "Chandigarh University" )
	tab stem_code
	set seed 10101	
	bys stem_code: g rand_num=runiform()
	bys stem_code: egen ordering=rank(rand_num)
	tostring ordering, replace
	replace ordering = "0" + ordering if strlen(ordering)==1
	g mentorid = "MS" + stem_code + ordering 
	
	keep mentor*
	
	save "$randomization/randomization_mentor_STEM.dta", replace

* Open mentee dataset 
import excel using "$randomization/randomization_student_STEM_mainlist.xlsx", sheet("Sheet1") firstrow clear

* STEM codes 
merge m:1 schoolname using "$clean/list_of_schools.dta", keepusing(stem*) keep(match) nogen

g stem_code = "CST" if stem_cst==1
replace stem_code = "JNE" if stem_jnec==1
replace stem_code = "GCI" if stem_gcit==1
replace stem_code = "SHR" if stem_shrubtse==1
replace stem_code = "CNR" if stem_cnr==1
replace stem_code = "RTC" if stem_rtc==1

set seed 10101
bys schoolname: g rand_num=runiform()
bys schoolname: egen ordering=rank(rand_num)

g mentor = .
forval i = 01/30 {
	sca l = (`i'-1)*5
	sca h = `i'*5
	dis "`=scalar(l)'"
	dis "`=scalar(h)'"
	replace mentor = `i' if ordering >`=scalar(l)' & ordering<=`=scalar(h)'
}
sort stem_code schoolname mentor
egen groupmentor1 = group(stem_code schoolname mentor) if stem_code=="CST"
egen groupmentor2 = group(stem_code schoolname mentor) if stem_code=="JNE"
egen groupmentor3 = group(stem_code schoolname mentor) if stem_code=="GCI"
egen groupmentor4 = group(stem_code schoolname mentor) if stem_code=="SHR"
egen groupmentor5 = group(stem_code schoolname mentor) if stem_code=="CNR"
egen groupmentor6 = group(stem_code schoolname mentor) if stem_code=="RTC"
egen groupmentor=rowmax(groupmentor1-groupmentor6)
tostring groupmentor, replace
replace groupmentor = "0" + groupmentor if strlen(groupmentor)==1

g mentorid = "MS" + stem_code + groupmentor 

keep schoolname name studentid sex b_day b_month b_year phone mentorid

* merge with mentor 
merge m:1 mentorid using "$randomization/randomization_mentor_STEM.dta"
preserve 
keep if _merge==2
sort mentorid 
keep mentorid mentor_name mentor_email mentor_phone mentor_stem mentor_stream
export excel using "$randomization/list_of_extra_mentors_STEM.xlsx", firstrow(variables) replace
restore 

keep if _merge==3
drop _merge

sort mentorid name
by mentorid: g id=_n
tostring id, replace
replace id = "0" + id if strlen(id)==1

g menteeid = mentorid + id
drop id

sort mentorid mentor_name mentor_email menteeid schoolname name studentid 
order mentorid mentor_name mentor_email mentor_phone menteeid schoolname name studentid 

export excel using "$randomization/final/randomization_STEM_mainlist_$date.xlsx", firstrow(variables) replace